package com.utbm.lo54.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.utbm.lo54.dao.LocationDAOInterface;
import com.utbm.lo54.entity.Client;
import com.utbm.lo54.entity.Course;
import com.utbm.lo54.entity.Location;

public class MysqlLocationDAO implements LocationDAOInterface {

	private DataSource dataSource;

	public MysqlLocationDAO() {
		super();
		try {
			Context namingContext = new InitialContext();
			dataSource = (DataSource) namingContext.lookup("java:comp/env/jdbc/testDatasource");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	@Override
	public Location create(Location lo) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("INSERT INTO COURSE.LOCATION(CITY_LOCATION) VALUES (?)", new String[] { "ID_LOCATION" });
			stmt.setString(1, lo.getCity());
			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				lo.setId(rs.getLong(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return lo;
	}

	@Override
	public Location findById(Long id) {
		Connection connection = null;
		Location lo = new Location();
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("Select CITY_LOCATION FROM COURSE.LOCATION WHERE ID_LOCATION=?");
			stmt.setLong(1, id);

			ResultSet set = stmt.executeQuery();
			while(set.next()){
				lo.setCity(set.getString(1));
				lo.setId(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return lo;
	}

	@Override
	public Location findByCity(String city) {
		Connection connection = null;
		Location lo = new Location();
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("Select ID_LOCATION FROM COURSE.LOCATION WHERE CITY_LOCATION=?");
			stmt.setString(1, city);

			ResultSet set = stmt.executeQuery();
			while(set.next()){
				lo.setCity(city);
				lo.setId(set.getLong(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return lo;
	}

	@Override
	public void update(Location lo) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delete(Location lo) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			Statement stmt = connection.createStatement();
			stmt.executeQuery("DELETE FROM COURSE.LOCATION WHERE ID_LOCATION =" + lo.getId());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public Set<Location> getAllLocation() {
		Connection connection = null;
		Set<Location> list = new HashSet<Location>();
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("Select ID_LOCATION,CITY_LOCATION FROM COURSE.LOCATION");
			

			ResultSet set = stmt.executeQuery();
			while(set.next()){
				Location lo = new Location();
				lo.setId(set.getLong(1));
				lo.setCity(set.getString(2));
				list.add(lo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return list;
	}



}
